﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;

namespace DBHelper
{
   public class SqlHelper
   {
      public static bool TestConnection()
      {
         string connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
         SqlConnection connection = null;
         try
         {
            connection = new SqlConnection(connectionString);
            connection.Open();

            return true;
         }
         catch
         {
            return false;
         }
         finally
         {
            if (connection != null && connection.State == ConnectionState.Open)
            {
               connection.Close();
               connection.Dispose();
            }
         }
      }

      public static SqlDataReader GetReader(string query)
      {
         return GetReader(query, CommandType.Text);
      }

      public static SqlDataReader GetReader(string query, CommandType commandType)
      {
         return GetReader(query, CommandType.Text, null);
      }

      public static SqlDataReader GetReader(string query, CommandType commandType, SqlParameter[] parameters)
      {
         SqlConnection connection = null;
         SqlCommand command = null;

         try
         {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

            command = new SqlCommand(query, connection);

            command.CommandType = commandType;

            if (parameters != null && parameters.Length > 0)
            {
               command.Parameters.AddRange(parameters);
            }

            connection.Open();

            return command.ExecuteReader(CommandBehavior.CloseConnection);
         }
         catch (Exception ex)
         {
            throw ex;
         }
      }

      public static int ExecuteNonQuery(string query)
      {
         SqlParameter returnValue = null;
         return ExecuteNonQuery(query, null, CommandType.Text, ref returnValue);
      }

      public static int ExecuteProcedure(string procedureName, SqlParameter[] parameters)
      {
         SqlParameter returnValue = null;
         return ExecuteNonQuery(procedureName, parameters, CommandType.StoredProcedure, ref returnValue);
      }

      public static int ExecuteNonQuery(string query, SqlParameter[] parameters, CommandType type, ref SqlParameter returnValue)
      {
         using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
         {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
               command.CommandType = type;

               if (parameters != null && parameters.Length > 0)
               { command.Parameters.AddRange(parameters); }

               if (returnValue != null)
               {
                  command.Parameters.Add(returnValue);
               }

               connection.Open();
               return command.ExecuteNonQuery();
            }
         }
      }
   }
}
